package dataClean;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.Serializable;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.List;

import static org.apache.poi.ss.usermodel.Cell.*;
/**
 * @author Smartloe
 * @create 2021-05-11 16:50
 */
public class ExcelClean3 {
    public static void main(String[] args) {
        String fileName1 = "C:\\Users\\25392\\Desktop\\Java基础\\src\\main\\java\\dataClean\\data\\excelclean3test.xlsx";
        poiExcel(fileName1);
    }

    //清洗缺失值（替换：错误年龄，空值，无效值,最大值，最小值，平均值）
    public static void poiExcel(String fileName) {
        boolean is2007 = true;    //判断是否是excel2007格式
        if (fileName.endsWith("xlsx")) {
            is2007 = false;
        }
        try {
            InputStream input = new FileInputStream(fileName);  //建立输入流
            Workbook wb;
            //根据文件格式(2003或者2007)来初始化
            if (is2007) {
                wb = new HSSFWorkbook(input);
            } else {
                wb = new XSSFWorkbook(input);
            }
            //getSheetAt(index:0)表示从wb中获得第一个表格对象。
            Sheet sheet = wb.getSheetAt(1);
            List<List<Serializable>> lst_total = new ArrayList<List<Serializable>>();
            List<Serializable> lst_age = new ArrayList<Serializable>();
            int lastRowNum = sheet.getLastRowNum();
            int max_age = 0;
            int min_age = 50;
            int sum_age =0;
            int count = 0;
            boolean flag;
            double age_count_invalid = 0;
            double age_count_valid = 0;
            for (int i = 1; i <= lastRowNum; i++) {
                //这里很重要,因为在这里定义ArrayList,保证了每次行循环的时候,都是一个空的lst去接当前行的数据.
                List<Serializable> lst = new ArrayList<Serializable>();
                //具体到某一行
                Row row = sheet.getRow(i);
                /*
                这里可以举一反三，通过操作每行row的单元cell数组下标，进行判断和处理所有数据。并将单列数据单独存储，最后与其它数据整合。
                 */
                //获得有效年龄值
                double age1 = row.getCell(7).getNumericCellValue();
                int age_int1 = (int) age1;
                if (age_int1 > 20 && age_int1 < 60) {
                    age_count_valid++;
                    lst_age.add(age_int1);
                    flag = true;
                } else {
                    age_count_invalid++;
                    age_int1 = 60;
                    lst_age.add(age_int1);
                    flag = false;
                }
                //单元格的数量
                int lastCellNum = row.getLastCellNum();
                for (int j = 0; j < lastCellNum - 1; j++) {
                    Cell cell = row.getCell(j);
                    //根据cell中的类型来输出数据
                    switch (cell.getCellType()) {
                        case CELL_TYPE_NUMERIC:
                            //这里用于区分年龄和发货日期。年龄在20-100之间的数字都是合法年龄，因为日期数据比较大。
                            //因此，这里可以发现非法年龄。
                            if (cell.getNumericCellValue() > 20 && cell.getNumericCellValue() < 100) {
                                double age = cell.getNumericCellValue();
                                int age_int = (int) age;
                                lst.add(age_int);
                            } else {
                                //清洗日期数据格式。
                                short format = cell.getCellStyle().getDataFormat();
                                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                                if (format != 0) {
//                            format == 176时是当指定单元格格式为日期是都是176
                                    if (format == 14 || format == 31 || format == 57 || format == 58 || format == 176) {
                                        //日期
                                        sdf = new SimpleDateFormat("yyyy-MM-dd");
                                    } else if (format == 20 || format == 32) {
                                        //时间
                                        sdf = new SimpleDateFormat("HH:mm");
                                    }
                                    double value = cell.getNumericCellValue();
                                    Date date = DateUtil.getJavaDate(value);
                                    String result = sdf.format(date);
                                    lst.add(result);
                                }
                            }
                            break;
                        case CELL_TYPE_STRING:
//                            System.out.print(cell.getStringCellValue());
//                            System.out.print("|");
                            //对所有内容为"unknown"的单元进行替换。
                            if (cell.getStringCellValue().equals("不知道")) {
                                String s = cell.getStringCellValue();
                                s = "无效值";
                                lst.add(s);
                            } else {
                                lst.add(cell.getStringCellValue());
                            }
                            break;
                        case CELL_TYPE_BOOLEAN:
//                            System.out.print(cell.getBooleanCellValue());
//                            System.out.print("|");
                            lst.add(cell.getBooleanCellValue());
                            break;
                        case CELL_TYPE_FORMULA:
//                            System.out.print(cell.getCellFormula());
//                            System.out.print("|");
                            lst.add(cell.getCellFormula());
                            break;
                        case CELL_TYPE_BLANK:
//                            System.out.print("blank");
//                            System.out.print("|");
                            //用于判断和填充空值。
                            lst.add("NULL");
                            break;
                        case CELL_TYPE_ERROR:
//                            System.out.print("error");
//                            System.out.print("|");
                            lst.add("error");
                            break;
                        default:
//                            System.out.print("unsuported sell type");
//                            System.out.print("|");
                            lst.add("unsuported sell type");
                            break;
                    }
                }
                lst_total.add(lst);
            }

            //求年龄最大值
            for (Serializable item : lst_age) {
                int age = (int) item;
                if (age > max_age) {
                    max_age = age;
                }
            }
            //求年龄最小值
            for (Serializable value : lst_age) {
                int age = (int) value;
                if (age < min_age) {
                    min_age = age;
                }
            }

            //求年龄平均值
            for (Serializable serializable : lst_age) {
                int age = (int) serializable;
                sum_age += age;
                count++;
            }
            System.out.println("最大有效年龄："+max_age);
            System.out.println("最小有效年龄："+min_age);
            System.out.println("平均年龄："+ sum_age/count);
            System.out.println("年龄数据有效率:"+age_count_valid/(age_count_invalid+age_count_valid));
            //实现两个List<java.io.Serializable>的合并。
            int i;
            for (i = 0; i < lst_total.size(); i++) {
                lst_total.get(i).add(lst_age.get(i));
            }

            //获得第一行第一列
            //给新增的一列数据赋值
            List<Serializable> lst_bank = new ArrayList<Serializable>();
            lst_bank.add("中国银行");
            int j;
            for (j = 0; j < lst_total.size(); j++) {
                //新增一列数据
                lst_total.get(j).add(8, lst_bank.get(0));
            }

            //删除重复的数据
            String readLine = null;
            int p=0;
            //哈希码产生的依据：哈希码并不是完全唯一的，它是一种算法，让同一个类的对象按照自己不同的特征尽量的有不同的哈希码，但不表示不同的对象哈希码完全不同
            Map<String, String> map = new HashMap<String, String>();
            // 声明一个String对象，用于接收临时变量
            for (List<Serializable> serializables : lst_total) {
                if (!map.containsValue(serializables.toString())) {
                    // 给第一次出现的值赋予一个新的“key+i”作为键。
                    map.put("key" + p, serializables.toString());
                    p++;
                }
            }

            //清洗第一列和最后一列的“[”和“]”符号
            ArrayList<String[]> lst_map = new ArrayList<String[]>();
            String[] ss = null;
            //使用for循环遍历出map的值。通过的map.get("key" + j)的形式。
            for (int h = 0; h < map.size(); h++) {
                ss =  map.get("key" + h).split(",");
                ss[8] = ss[8].replace("]","");
                ss[0] = ss[0].replace("[","");
                lst_map.add(ss);
            }

            //开始导入清洗之后的数据
            int t;
            ExcelClean3 EC = new ExcelClean3();
            for (String[] s1: lst_map) {
                String id = s1[0];
                String name = s1[1];
                String date = s1[2];
                String location1= s1[3];
                String location2= s1[4];
                String type = s1[5];
                String gender = s1[6];
                String age = s1[7];
                String bank = s1[8];
                EC.mysql_conn(id,name,date,location1,location2,type,gender,age,bank);
            }
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    //将数据导入MYSQL
    public void mysql_conn(String id , String name, String date,String location1, String location2, String type,String gender, String age,String bank){
        // MySQL 8.0 以下版本 - JDBC 驱动名及数据库 URL
//        final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
        //这个是安全连接的问题，在sql连接字符串中，添加关于不使用SSL访问数据数据库的说明：useSSL=false。
//        final String DB_URL = "jdbc:mysql://localhost:3306/test?&useSSL=false";

        // MySQL 8.0 以上版本 - JDBC 驱动名及数据库 URL
        final String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";
        final String DB_URL = "jdbc:mysql://localhost:3306/RUNOOB?useSSL=false&serverTimezone=UTC";


        // 数据库的用户名与密码，需要根据自己的设置
        final String USER = "root";
        final String PASS = "147258";

        Statement stmt = null;
        java.sql.Connection conn = null;
        try{
            // 注册 JDBC 驱动
            Class.forName(JDBC_DRIVER);

            // 打开链接
            System.out.println("连接数据库...");
            conn = DriverManager.getConnection(DB_URL,USER,PASS);

            // 执行查询
            System.out.println(" 实例化Statement对象...");
            stmt = conn.createStatement();
            String sql;
            sql = "insert into excelclean3test (id, namedemo, datedemo,location1,location2,typedemo,gender,age,bank) values ('"+id+"','"+name+"','"+date+"','"+location1+"','"+location2+"','"+type+"','"+gender+"','"+age+"','"+bank+"')";
            stmt.execute(sql);
            stmt.close();
            conn.close();
        } catch(
                Exception se){
            // 处理 JDBC 错误
            se.printStackTrace();
        }// 处理 Class.forName 错误
        finally{
            // 关闭资源
            try{
                if(stmt!=null) stmt.close();
            }catch(SQLException ignored){
            }// 什么都不做
            try{
                if(conn!=null) conn.close();
            }catch(SQLException se){
                se.printStackTrace();
            }
        }
        System.out.println("Goodbye!");
    }
}
